package cn.bytecollege.common.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 数据库操作工具类
 * @author MR.W
 */
public class DBHelper {
	private static String driver = null;
	private static String url = null;
	private static String user = null;
	private static String password = null;
	private Connection connection = null;
	private PreparedStatement statement = null;
	private ResultSet rs = null;
	private static DruidDataSource dataSource = null;

	static {
		//加载配置文件
		InputStream is = DBHelper.class.getClassLoader().getResourceAsStream("data.properties");
		Properties p = new Properties();
		try {
			p.load(is);
			dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(p);
		} catch (IOException e) {
			System.out.println("读取配置文件异常！");
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取连接
	 * @throws SQLException
	 */
	private void getConnection() throws SQLException {
		if(connection==null||connection.isClosed()) {
			connection = dataSource.getConnection();
		}
	}
	/**
	 * 执行增删改的方法
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public int executeUpdate(String sql,Object...objects){
		int k = 0;
		try {
			getConnection();
			statement = connection.prepareStatement(sql);
			setObject(objects);
			k = statement.executeUpdate();
		}catch (SQLException e){
			System.out.println("执行executeUpdate异常");
			e.printStackTrace();
		}
		return k;
	}
	/**
	 * 执行查询的方法
	 * @param <T>
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws SecurityException
	 * @throws NoSuchFieldException
	 */
	public <T> List<T> executeQuery(String sql,Class<T> clazz,Object...objects){
		ArrayList<T> list = new ArrayList<>();
		try{
			getConnection();
			statement = connection.prepareStatement(sql);
			setObject(objects);
			rs = statement.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int count = metaData.getColumnCount();
			//遍历rs
			while(rs.next()) {
				T t = null;
				try {
					t = clazz.newInstance();
					for (int i = 1; i <=count; i++) {
						//获取每一个字段的名称
						String columnName = metaData.getColumnName(i);
						//根据名称获取每个字段的值
						Object o = rs.getObject(columnName);
						//将字段名转换成驼峰命名法
						String fieldName = toCamelName(columnName);
						//通过反射获取数据库字段对应实体类中的实例变量
						Field field = clazz.getDeclaredField(fieldName);
						//因为实例变量是private修饰的，因此要设置可访问
						field.setAccessible(true);
						field.set(t, o);
					}
				} catch (NoSuchFieldException e) {
					e.printStackTrace();
				} catch (InstantiationException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
				list.add(t);
			}
		}catch (SQLException e){
			System.out.println("执行查询异常");
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 经典命名法转驼峰命名法
	 * @param name
	 * @return
	 */
	public String toCamelName(String name) {
		//转小写
		name = name.toLowerCase();
		//分割
		String[] names = name.split("_");
		StringBuilder sb = new StringBuilder(names[0]);
		for (int i = 1; i < names.length; i++) {
			String index = names[i].substring(0,1);
			index = index.toUpperCase();
			sb.append(index).append(names[i].substring(1));
		}
		return sb.toString();
	}

	/**
	 * 执行聚合函数
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public int executeScale(String sql,Object...objects) {
		int result = 0;
		try {
			getConnection();
			statement = connection.prepareStatement(sql);
			setObject(objects);
			rs = statement.executeQuery();
			while (rs.next()){
				result = rs.getInt(1);
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return result;
	}
	/**
	 * 为占位符赋值
	 * @param objects
	 * @throws SQLException
	 */
	public void setObject(Object...objects) throws SQLException {
		if(objects!=null&&objects.length>0) {
			for (int i = 0; i < objects.length; i++) {
				statement.setObject((i+1), objects[i]);
			}
		}
	}
	/**
	 * 关闭资源
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		if(rs!=null&&!rs.isClosed()) {
			rs.close();
			rs = null;
		}
		if(statement!=null&&!statement.isClosed()) {
			statement.close();
			statement = null;
		}
		if(connection!=null&&!connection.isClosed()) {
			connection.close();
			connection = null;
		}
	}

}
